"""
Clones existing legacy database into new one.
"""

from app import app, db
import os
import mysql.connector
from app.models import *



os.environ['DATABASE_URL'] = '' # make sure that we use app.db
db.drop_all()
db.create_all()

databaseconnection = mysql.connector.connect(user='root', password='test', database='gem')
c = databaseconnection.cursor()

# Legacy stuff
departmentdict = {}
sciencedomaindict = {}
coursesdict = {}

jobdict = {}
jobtypedict = {}

employees = {}
educationlevel = {}

def cloneemployees():
	print 'clonning employees table...'
	global employees, courses

	c.execute('SELECT * FROM Employee;')
	
	for row in c.fetchall():
		e = Employee()
	
		id = row[0]
		e.employeefirstname = employeename = row[1]
		e.employeesurname = employeesurname = row[2]
		e.employeegender = employeegender = row[3]
		e.employeejmbg = employeejmbg = row[4]
		e.employeephoto = photo = row[5]
		e.employeedateofbirth = employeebirthdate = row[6]
		e.employeecountryofbirth = employeecountryofbirth = row[7]
		e.employeestateofbirth = employeestateofbirth = row[8]
		e.employeecityofbirth = employeecityofbirth = row[9]
		e.employeedistrictofbirth = employeedistrictofbirth = row[10]
		e.employeestreet = employeestreet = row[11]
		e.employeecountry = employeecountry = row[12]
		e.employeestate = employeestate = row[13]
		e.emploeedistrict = emploeedistrict = row[14]
		e.employeecity = employeecity = row[15]
		workingyears = row[16]
		workingmonths = row[17]
		e.employeeeducationlevel = employeeeducationlevel = row[18]
		e.employeejobdate = employeejobdate = row[19]
		e.employeejobstatus = employeejobstatus = row[20]
		e.employeejobexpirationdate = employeejobexpirationdate = row[21]
		e.employeepersonalcardnumber = employeeblk = row[22]
		e.employeesecuritysocialnumber = employeelbo = row[23]
		e.employeemidname = employeemidname = row[24]
		e.employeeworkingdays = employeeworkingdays = row[25]
	
		db.session.add(e)

		employees[id] = e
	
	db.session.commit()
	print 'employees table clonned successfully!'

def clonedepartments():
	print 'Cloning departments....'
	global c
	# Clone Departments, ScienceDomains and Courses without relationship
	global departmentdict
	global sciencedomaindict
	global coursesdict
	c.execute('SELECT * FROM Department;')
	for row in c.fetchall():
		d = Department()
		d.departmenttitle = row[1]		
		departmentdict[row[0]] = d

	c.execute('SELECT * FROM ScienceDomain;')
	for row in c.fetchall():
		s = ScienceDomain()
		s.sciencedomaintitle = row[1]
		sciencedomaindict[row[0]] = s

	c.execute('SELECT * FROM Course;')
	for row in c.fetchall():
		crs = Course()
		crs.coursetitle = row[1]
		coursesdict[row[0]] = crs
	
	c.execute('SELECT * FROM ScienceDomainCourses;')
	for row in c.fetchall():
		course = coursesdict[row[0]]
		sciencedomain = sciencedomaindict[row[1]]
		department = departmentdict[row[2]]

		if sciencedomain not in department.sciencedomains:
			department.sciencedomains.append(sciencedomain)

		if course not in sciencedomain.courses:
			sciencedomain.courses.append(course)
		
	
	for department in departmentdict.values():
		db.session.add(department)

	db.session.commit()
	print('Clonning done successfully!')
	
def cloneemployeecourses():
	print 'Clonning employeecourse table'
	global employees, coursesdict
	global c

	c.execute('SELECT * FROM employeecourse')
	for row in c.fetchall():
		try:
			e = employees[row[0]]
			ec = coursesdict[row[1]]
			ec.employeecoursestartdate = None
			ec.employeecourseenddate = None
			e.employeecourses.append(ec)

			#print unicode(u'Fetched employee %s and course %s' % (e.employeefirstname, coursesdict[row[1]].coursetitle))
			db.session.add(e)
		except KeyError:
			print('Key not found')
			pass
	db.session.commit()
	print 'Clonning employeecourse successfully!'

def cloneeducationlevels():
	print 'Clonning educationlevels...'
	global educationlevel,c, employees
	c.execute('SELECT * FROM educationlevel')
	for row in c.fetchall():
		e = EducationLevel()
		e.educationleveltitle = row[1]
		e.educationleveltag = row[2]
		db.session.add(e)
		educationlevel[row[0]] = e

	db.session.commit()
	print 'Conning educationlevels successfully!'
	print 'clonning employeeeducation...'
	c.execute('SELECT * FROM EmployeeEducation')
	for row in c.fetchall():
		try:
			e = employees[row[0]]
			eed = educationlevel[row[1]]
			#eed.educationlevel = educationlevel[row[1]]
			eed.employeeeducationfacultytitle = row[2]
			eed.employeeeducationgraduatedate = row[3]
			eed.employeeeducationdepartment = row[4]
			eed.employeeeducationscienceinteresttitle = row[5]

			e.employeeeducations.append(eed)					
			db.session.add(e)
			db.session.add(eed)
		except KeyError:
			pass

	db.session.commit()
	print 'clonning employeeeducation done!'

def clonejobtypes():
	print ('Clonning jobs...')
	global c
	c.execute('SELECT * FROM Job')
	for row in c.fetchall():
		job = Job()
		job.jobtitle = row[1]
		jobdict[row[0]] = job

	c.execute('SELECT * FROM JobType')
	for row in c.fetchall():
		jobtype = JobType()
		jobtype.jobtypetitle = row[2]
		jobtype.jobtypeduration = row[3]
		jobtype.jobtypebasepoints = row[4]
		jobtype.jobtypecoefficient = row[5]

		jobtypedict[row[1]] = jobtype
		jobdict[row[0]].jobtypes.append(jobtype)
		
	for job in jobdict.values():
		db.session.add(job)

	db.session.commit()
	print('clonning jobs, done!')

def cloneemployeejobtypes():
	global c
	c.execute('SELECT * FROM EmployeeJobType')
	for row in c.fetchall():
		try:
			employee = employees[row[5]]
			jobtype = jobtypedict[row[1]]
			jobtype.employeejobtypestartdate = row[2]
			jobtype.employeejobtypeexpirationdate = row[3]
			jobtype.employeejobtypeactive = row[4]
			employee.employeejobs.append(jobtype)
			db.session.add(employee)
		except KeyError:
			pass
	db.session.commit()

if __name__ == u'__main__':
	clonedepartments()
	cloneemployees()
	cloneemployeecourses()
	cloneeducationlevels()
	clonejobtypes()
	cloneemployeejobtypes()
	